import xlwings
import pymysql

x_app = xlwings.App(visible=False, add_book=False)
x_app.display_alerts = False
x_app.screen_updating = False


class MyDataBase:
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            port=3306,
            db='337web',
            charset='utf8'
        )

    # 查找所有数据
    def all(self, table_name):
        sql = """select * from {};""".format(table_name)
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute(sql)
        return cursor.fetchall()

    # 模糊查询单一数据
    def find(self, table_name, data_key, data_value):
        sql = """select * from {} where {} like""".format(table_name, data_key)
        sql += "'%{}%'".format(data_value)
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute(sql)
        return cursor.fetchall()

    # 查找单一数据
    def info(self, table_name, data_key, data_value):
        sql = """select * from {} where {}=""".format(table_name, data_key)
        sql += "'{}';".format(data_value)
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute(sql)
        return cursor.fetchall()

    # 插入数据 info为字典 {名称:值}
    def insert(self, table_name, info):
        cursor = self.conn.cursor()
        sql = """insert into {} (""".format(table_name)
        for i in info.keys():
            sql += "{}, ".format(i)
        if sql[len(sql) - 1] == ' ':
            sql = sql[:-2]
        sql += ") values("
        for i in info.keys():
            if type(info[i]) == str:
                sql += "'{}', ".format(info[i])
            else:
                sql += "{}, ".format(info[i])
        if sql[len(sql) - 1] == ' ':
            sql = sql[:-2]
        sql += ');'
        cursor.execute(sql)
        self.conn.commit()

    # 查询表格是否存在
    def tables_empty(self, table_name):
        sql = """show tables like '{}';""".format(table_name)
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute(sql)
        return cursor.fetchall()

    # 创建一个新的表格 info为存放条目信息的列表 {名称:{类型, 大小, 是否不能为空, 是否为主键}}\
    # kind size is_null is_prime
    def create(self, table_name, info):
        if len(self.tables_empty(table_name)) != 0:
            return 'exists'
        sql = """create table {}(""".format(table_name)
        for i in info.keys():
            sql += "{} {}({})".format(i, info[i]['kind'], info[i]['size'])
            if info[i]['is_prime']:
                sql += " primary key"
            elif info[i]['is_null']:
                sql += ' not null'
            sql += ','
        if len(info) != 0:
            sql = sql[:-1]
        sql += ');'
        cursor = self.conn.cursor()
        cursor.execute(sql)
        return 'success'

    # 修改数据 info [{key:new_data, key:new_data, ....}, {position:data, position:data, ....}]
    # update test set
    def change(self, table_name, info):
        if len(info[0]) == 0:
            return 'exit'
        cursor = self.conn.cursor()
        change_data = ""
        for i in info[0].keys():
            if type(info[0][i]) == str:
                change_data += "{}='{}',".format(i, info[0][i])
            else:
                change_data += "{}={},".format(i, info[0][i])
            change_data = change_data[:-1]
        sql = """update {} set {}""".format(table_name, change_data)
        if len(info[1]) != 0:
            sql += ' where '
            where_data = ""
            for i in info[0]:
                if type(info[0][i]) == str:
                    where_data += "{}='{}',".format(i, info[0][i])
                else:
                    where_data += "{}={},".format(i, info[0][i])
            where_data = where_data[:-1]
            sql += '{};'.format(where_data)
        else:
            sql += ';'
        cursor.execute(sql)
        self.conn.commit()
